﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SimpleDatabaseAdapter
{
    internal class SQLPatterns
    {
        internal static string GetValue(object value, AllDataColumnTypes type)
        {
            if (value == null)
            {
                return SQL_NULL;
            }

            if (type == AllDataColumnTypes.Int || type == AllDataColumnTypes.Float)
            {
                return value.ToString();
            }

            string valueStr = string.Format(VALUE_STRING_PATTERN, value);
            return valueStr;
        }

        internal const string SQL_NULL = "NULL";
        internal const string VALUE_STRING_PATTERN = "'{0}'";
        internal const string COLUMN_PATTERN = "[{0}]";
        internal const string TABLE_PATTERN = "[{0}]";
        internal const string SET_VALUE_PATTERN = "[{0}] = {1}";
        internal const string SELECT_ALL_COLUMNS = "*";

        internal const string ADD_ITEM_PATTERN = "INSERT INTO [{0}] ({1}) VALUES ({2});";
        internal const string SELECT_SCOPE_IDENTITY = "SELECT SCOPE_IDENTITY();";
        internal const string UPDATE_ITEM_PATTERN = "UPDATE [{0}] SET {1} WHERE [{2}] = {3};";
        internal const string DELETE_ITEM_PATTERN = "DELETE FROM [{0}] WHERE [{1}] = {2};";

        internal const string WHERE_LIST_ID_PATTERN = "WHERE [{0}] = {1}";
        internal const string WHERE_WITH_LIST_ID_PATTERN = "WHERE ({0}) AND ([{1}] = {2})";
        internal const string ORDER_BY_PATTERN = "ORDER BY {0}";
        internal const string SELECT_PATTERN = "SELECT {0} FROM [{1}] {2} {3};";
        internal const string ADD_LIST_ID_PATTERN = "({0}) AND ([{1}] = {2})";
        internal const string LIST_ID_PATTERN = "[{0}] = {1}";

        /***** Lists *****/
        internal const string SQL_QUERY_GET_ALL_LISTS_PATTERN = "SELECT * FROM [{0}];";
        internal const string SQL_QUERY_ADD_LIST_PATTERN = "INSERT INTO [{0}] ([{1}] ,[{2}] ,[{3}]) VALUES ('{{0}}', '{{1}}', '{{2}}');";
        internal const string SQL_QUERY_UPDATE_LIST_PATTERN = "UPDATE [{0}] SET [{2}] = '{{1}}', [{3}] = '{{2}}', [{4}] = '{{3}}' WHERE [{1}] = {{0}};";
        internal const string SQL_QUERY_DELETE_LIST_PATTERN = "DELETE FROM [{0}] WHERE [{1}] = {2};";
        /***** /Lists *****/

        /***** TableCreator *****/
        internal const string SQL_QUERY_CHECK_EXISTS_TABEL_PATTERN = "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) BEGIN {1} END;";
        internal const string SQL_QUERY_ALL_DATA_CREATE_TABEL_PATTERN = @"CREATE TABLE [dbo].[{0}] (
                                                                    [{1}]   BIGINT IDENTITY (1, 1) NOT NULL,
                                                                    CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{1}] ASC)
                                                                );";
        internal const string SQL_QUERY_LISTS_CREATE_TABEL_PATTERN = @"CREATE TABLE [{0}] (
                                                                    [{1}]   INT IDENTITY (1, 1) NOT NULL,
                                                                    CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{1}] ASC)
                                                                );";

        internal const string SQL_QUERY_ALL_DATA_CREATE_COLUMN_CREATE_DATE_PATTERN = "ALTER TABLE [dbo].[{0}] ADD [{1}]   DATETIME    NOT NULL;";
        internal const string SQL_QUERY_ALL_DATA_CREATE_COLUMN_MODIFY_NAME_PATTERN = "ALTER TABLE [dbo].[{0}] ADD [{1}]   DATETIME    NOT NULL;";
        internal const string SQL_QUERY_ALL_DATA_CREATE_COLUMN_LIST_ID_PATTERN = "ALTER TABLE [dbo].[{0}] ADD [{1}]   INT   NOT NULL;";

        internal const string SQL_QUERY_LISTS_CREATE_COLUMN_TITLE_PATTERN = "ALTER TABLE [{0}] ADD [{1}]   NVARCHAR (150) NOT NULL;";
        internal const string SQL_QUERY_LISTS_CREATE_COLUMN_STATIC_NAME_PATTERN = "ALTER TABLE [{0}] ADD [{1}]   NVARCHAR (150) NOT NULL;";
        internal const string SQL_QUERY_LISTS_CREATE_COLUMN_COLUMNS_PATTERN = "ALTER TABLE [{0}] ADD [{1}]   NVARCHAR (MAX) NOT NULL;";

        internal const string SQL_QUERY_ALL_DATA_ALERT_TABLE_PATTERN = "ALTER TABLE [dbo].[{0}] ADD {{0}};";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_NVARCHAR_255_PATTERN = "[{0}_{{0}}] NVARCHAR (255) NULL";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_NVARCHAR_MAX_PATTERN = "[{0}_{{0}}] NVARCHAR (MAX) NULL";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_INT_PATTERN = "[{0}_{{0}}] INT NULL";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_FLOAT_PATTERN = "[{0}_{{0}}] FLOAT (53) NULL";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_BIT_PATTERN = "[{0}_{{0}}] BIT NULL";
        internal const string SQL_QUERY_ALL_DATA_COLUMN_DATETIME_PATTERN = "[{0}_{{0}}] DATETIME NULL";
        /***** /TableCreator *****/
        
    }
}
